﻿//-----------------------------------------------------------------------
// <copyright file="DataAccess.cs" company="Craig Longford">
//     Copyright (c) Craig Longford. All rights reserved.
//     Email: deltawolf7@gmail.com
//     WWW: http://www.deltasblog.co.uk
// </copyright>
//-----------------------------------------------------------------------

namespace Delta.Data.DataExpress
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Manipulates data from a database.
    /// </summary>
    public static class DataAccess
    {
        private const string exceptionMessageFail = "Unable to process request! An error occured while working with the database server.\nLast query ran: ";
        private const string exceptionNoConnection = "No connection name or connection defined.";

        #region Properties
        /// <summary>
        /// Gets or sets the connection string name stored in the application configuration file.
        /// </summary>
        public static string ConnectionName { get; set; }

        /// <summary>
        /// Gets or sets the database connection
        /// </summary>
        public static Connection Connection { get; set; }
        #endregion

        #region Get Single Object
        /// <summary>
        /// Fills an object of type T from the database, using the provided query and parameters.
        /// </summary>
        /// <typeparam name="T">Type of object to fill</typeparam>
        /// <param name="query">String query or Stored procedure</param>
        /// /// <param name="type">Command type</param>
        /// <param name="parameter">Parameter to pass with the query</param>
        /// <returns>Filled object of type T</returns>
        public static T GetSingle<T>(string query, CommandType type, Parameter parameter) where T : class
        {
            return GetSingle<T>(query, type, new Parameters(new Parameter[] { parameter }));
        }

        /// <summary>
        /// Fills an object of type T from the database, using the provided query and parameters.
        /// </summary>
        /// <typeparam name="T">Type of object to fill</typeparam>
        /// <param name="query">String query or Stored procedure</param>
        /// /// <param name="type">Command type</param>
        /// <param name="parameters">Parameters to pass with the query</param>
        /// <returns>Filled object of type T</returns>
        public static T GetSingle<T>(string query, CommandType type, Parameters parameters) where T : class
        {
            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, parameters, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), parameters, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            IDataServiceable entity = null;
            IDataReader reader = null;

            try
            {
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (entity == null)
                    {
                        entity = (IDataServiceable)Activator.CreateInstance(typeof(T));
                    }

                    entity.Fill(reader);
                }
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                }

                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return (T)entity;
        }

        /// <summary>
        /// Fills an object of type T from the database, using the provided query and parameters.
        /// </summary>
        /// <typeparam name="T">Type of object to fill</typeparam>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <returns>Filled object of type T</returns>
        public static T GetSingle<T>(string query, CommandType type) where T : class
        {
            return GetSingle<T>(query, type, new Parameters());
        }

        /// <summary>
        /// Fills an object from the database, using the provided query and parameters.
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// /// <param name="type">Command type</param>
        /// <param name="parameter">Parameter to pass with the query</param>
        /// <returns>Filled object containing result</returns>
        public static object GetSingle(string query, CommandType type, Parameter parameter)
        {
            return GetSingle(query, type, new Parameters(new Parameter[] { parameter }));
        }

        /// <summary>
        /// Fills an object from the database, using the provided query and parameters.
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// /// <param name="type">Command type</param>
        /// <param name="parameters">Parameters to pass with the query</param>
        /// <returns>Filled object containing result</returns>
        public static object GetSingle(string query, CommandType type, Parameters parameters)
        {
            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, parameters, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), parameters, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            IDataReader reader = null;
            object entity = null;

            try
            {
                command.Connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    entity = reader[0];
                }
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                }

                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return entity;
        }
        #endregion

        #region Get Collection
        /// <summary>
        /// Fills a collection of type C with object of type T from the database, using the provided query and parameters.
        /// </summary>
        /// <typeparam name="C">Type of collection to be filled</typeparam>
        /// <typeparam name="T">Type of object to fill the collection</typeparam>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <param name="parameters">Parameters to pass with the query</param>
        /// <returns>Filled collection of type C</returns>
        public static C GetCollection<C, T>(string query, CommandType type, Parameters parameters) where C : class
        {
            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, parameters, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), parameters, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }
     
            IList collection = (IList)Activator.CreateInstance(typeof(C));
            IDataReader reader = null;

            try
            {
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    IDataServiceable entity = (IDataServiceable)Activator.CreateInstance(typeof(T));
                    entity.Fill(reader);
                    collection.Add(entity);
                }
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                }

                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return (C)collection;
        }

        /// <summary>
        /// Fills a collection of type C with object of type T from the database, using the provided query and parameters.
        /// </summary>
        /// <typeparam name="C">Type of collection to be filled</typeparam>
        /// <typeparam name="T">Type of object to fill the collection</typeparam>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <returns>Filled collection of type C</returns>
        public static C GetCollection<C, T>(string query, CommandType type) where C : class
        {
            return GetCollection<C, T>(query, type, null);
        }
        #endregion

        #region Non Query Collection
        /// <summary>
        /// Execute an non query on the database
        /// </summary>
        /// <param name="collection">Collection of data serviceable objects</param>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param> 
        public static void NonQuery(IEnumerable collection, string query, CommandType type)
        {
            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, null, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), null, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            try
            {
                 using (command.Connection) 
                 { 
                      command.Connection.Open();

                      IDbTransaction transaction = command.Connection.BeginTransaction();
                      command.Transaction = transaction;

                      foreach (IDataServiceable item in collection) 
                      {
                          if (item is IDataServiceable)
                          {
                              command.Parameters.Clear();
                              item.GetSaveParameters().GetParameters(ref command);
                              command.ExecuteNonQuery();
                          }
                          else
                          {
                              throw new DataExpressException("Object or objects in collection do not implement the IDataServiceable interface.");
                          }
                      }

                      transaction.Commit();
                 } 
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }
        }
        #endregion

        #region Non Query Single Object
        /// <summary>
        /// Execute an non query on the database
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <param name="parameter">Parameter to pass with the query</param>   
        /// <returns>Number of returns affected</returns>
        public static int NonQuery(string query, CommandType type, Parameter parameter)
        {
            return NonQuery(query, type, new Parameters(new Parameter[] { parameter }));
        }

        /// <summary>
        /// Execute an non query on the database
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <param name="parameters">Parameters to pass with the query</param>   
        /// <returns>Number of returns affected</returns>
        public static int NonQuery(string query, CommandType type, Parameters parameters)
        {
            int returnInt = -1;

            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, parameters, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), parameters, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            try
            {
                command.Connection.Open();
                returnInt = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return returnInt;
        }

        /// <summary>
        /// Execute an non query on the database
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>  
        /// <param name="item">Data serviceable object</param>
        /// <returns>Number of returns affected</returns>
        public static int NonQuery(string query, CommandType type, IDataServiceable item)
        {
            int returnInt = -1;

            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, null, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), null, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            try
            {
                command.Connection.Open();
                item.GetSaveParameters().GetParameters(ref command);
                returnInt = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return returnInt;
        }

        /// <summary>
        /// Execute an non query on the database
        /// </summary>
        /// <param name="query">String query or Stored procedure</param>
        /// <param name="type">Command type</param>
        /// <returns>Number of returns affected</returns>
        public static int NonQuery(string query, CommandType type)
        {
            int returnInt = -1;

            IDbCommand command = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                command = Helper.CreateCommand(query, ConnectionName, null, type);
            }
            else if (Connection != null)
            {
                command = Helper.CreateCommand(query, Connection.GetConnection(), null, type);
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            try
            {
                command.Connection.Open();
                returnInt = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DataExpressException(exceptionMessageFail + query + "\n" + ex.Message, ex);
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }

            return returnInt;
        }
        #endregion

        #region Test Connection
        /// <summary>
        /// Test a connection
        /// </summary>
        /// <param name="exception">Database exception is one occurred</param>
        /// <returns>True if connection is ok else false</returns>
        public static bool TestConnection(out Exception exception)
        {
            IDbConnection connection = null;

            if (!string.IsNullOrEmpty(ConnectionName))
            {
                connection = Helper.GetConnection(ConnectionName);
            }
            else if (Connection != null)
            {
                connection = Connection.GetConnection();
            }
            else
            {
                throw new DataExpressException(exceptionNoConnection);
            }

            try
            {
                connection.Open();
            }
            catch (Exception ex)
            {
                exception = ex;
                return false;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            exception = null;
            return true;
        }
        #endregion
    }
}
